package day20171011;


import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Locale;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;
import java.sql.Connection;

/**
 * Created by 14394 on 2017/10/11.
 */
public class TestJDBC {
    private static ResourceBundle bundle= PropertyResourceBundle.getBundle("day20171011/db", Locale.getDefault());
    private static Connection conn;
    private static PreparedStatement ps;

    //创建表
    public  static void createTable(String tabName,String[] tab_fileds){
        //首先要获得连接
        conn=getConnection();
        try {
            String sql="create table "+tabName+"(id int auto_increment primary key not null,";
            if (tab_fileds!=null&&tab_fileds.length>0){
                sql+=" ";
                int length=tab_fileds.length;
                for (int i=0;i<length;i++){
                    sql+=tab_fileds[i].trim()+" varchar(50)";
                    if (i<length-1){
                        sql+=",";
                    }
                }
            }
            sql+=")DEFAULT CHARSET=utf8;";
            System.out.println("创建语句是"+sql);
            ps=conn.prepareStatement(sql);
            ps.executeUpdate(sql);
            ps.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println("建表失败"+e.getMessage());
        }
    }

    //获取数据库连接的函数
    private  static Connection getConnection(){
        Connection con=null;
        try {
            Class.forName(bundle.getString("db.classname"));//加载mysql数据驱动
            con= DriverManager.getConnection(bundle.getString("db.url"),bundle.getString("db.username"),bundle.getString("db.password"));
        }catch (Exception e){
            System.out.println("数据库连接失败"+e.getMessage());
        }
        return  con;
    }

    public static void insert(String tabName,String[] fields,String[] data){
        conn=getConnection();
        try {
            String sql="insert into "+tabName+"(";
            int length=fields.length;
            for (int i=0;i<length;i++){
                sql+=fields[i];
                if (i<length-1){
                    sql+=",";
                }
            }
            sql+=") values(";
            for (int i=0;i<length;i++){
                sql+="?";
                if (i<length-1){
                    sql+=",";
                }
            }
            sql+=");";
            System.out.println("添加数据的sql:"+sql);
            //预处理SQL 防止注入
            excutePs(sql,length,data);
            ps.executeUpdate();
            ps.close();
            conn.close();
        }catch (SQLException e){
            System.out.println("添加数据失败"+e.getMessage());
        }
    }

    //用于注入参数
    private static void excutePs(String sql,int length,String[] data) throws SQLException{
        ps=conn.prepareStatement(sql);
        for (int i=0;i<length;i++){
            ps.setString(i+1,data[i]);
        }
    }

    public static void main(String[] args) {
        String tabName="TestSpider";
        String[] tab_fields={"网页源码","url地址","抓取时间"};
        createTable(tabName,tab_fields);
        String[] data1={"html>head>body","www.baidu.com","152316658"};
        insert(tabName,tab_fields,data1);
    }

}
